{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a25fc2a3-d329-4361-9ea2-25472a5f5f4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os, json\n",
    "os.chdir(\"/Users/xiaosongw/Dropbox/Research/InformedSources/Replication/Build\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0def78a-a7b6-4cac-ac94-bfc86085daf9",
   "metadata": {},
   "source": [
    "# ACCC daily volume share in a quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8349866d-c963-42c4-85c0-7b10995a7963",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ACCC\n",
    "d_shr = {'2016-07-01': 3.8,\n",
    " '2016-07-02': 3.35,\n",
    " '2016-07-03': 3.1,\n",
    " '2016-07-04': 3.22,\n",
    " '2016-07-05': 3.4,\n",
    " '2016-07-06': 3.51,\n",
    " '2016-07-07': 3.9,\n",
    " '2016-07-08': 3.65,\n",
    " '2016-07-09': 3.5,\n",
    " '2016-07-10': 3.05,\n",
    " '2016-07-11': 3.4,\n",
    " '2016-07-12': 3.4,\n",
    " '2016-07-13': 3.8,\n",
    " '2016-07-14': 4.0,\n",
    " '2016-07-15': 3.85,\n",
    " '2016-07-16': 3.56,\n",
    " '2016-07-17': 3.22,\n",
    " '2016-07-18': 3.46,\n",
    " '2016-07-19': 3.75,\n",
    " '2016-07-20': 3.9,\n",
    " '2016-07-21': 4.25,\n",
    " '2016-07-22': 3.8,\n",
    " '2016-07-23': 2.78,\n",
    " '2016-07-24': 2.48,\n",
    " '2016-07-25': 2.71,\n",
    " '2016-07-26': 3.0,\n",
    " '2016-07-27': 3.3,\n",
    " '2016-07-28': 3.55,\n",
    " '2016-07-29': 3.6,\n",
    " '2016-07-30': 3.31,\n",
    " '2016-07-31': 3.2,\n",
    " '2016-08-01': 3.4,\n",
    " '2016-08-02': 3.75,\n",
    " '2016-08-03': 3.82,\n",
    " '2016-08-04': 4.05,\n",
    " '2016-08-05': 3.9,\n",
    " '2016-08-06': 3.58,\n",
    " '2016-08-07': 3.3,\n",
    " '2016-08-08': 3.32,\n",
    " '2016-08-09': 3.8,\n",
    " '2016-08-10': 3.85,\n",
    " '2016-08-11': 4.1,\n",
    " '2016-08-12': 3.9,\n",
    " '2016-08-13': 3.6,\n",
    " '2016-08-14': 3.51,\n",
    " '2016-08-15': 3.55,\n",
    " '2016-08-16': 3.75,\n",
    " '2016-08-17': 3.81,\n",
    " '2016-08-18': 4.0,\n",
    " '2016-08-19': 3.8,\n",
    " '2016-08-20': 3.62,\n",
    " '2016-08-21': 3.4,\n",
    " '2016-08-22': 3.45,\n",
    " '2016-08-23': 3.7,\n",
    " '2016-08-24': 3.9,\n",
    " '2016-08-25': 3.95,\n",
    " '2016-08-26': 3.8,\n",
    " '2016-08-27': 3.4,\n",
    " '2016-08-28': 3.15,\n",
    " '2016-08-29': 3.1,\n",
    " '2016-08-30': 2.9,\n",
    " '2016-08-31': 2.45,\n",
    " '2016-09-01': 2.8,\n",
    " '2016-09-02': 3.01,\n",
    " '2016-09-03': 2.76,\n",
    " '2016-09-04': 2.72,\n",
    " '2016-09-05': 3.22,\n",
    " '2016-09-06': 3.28,\n",
    " '2016-09-07': 3.5,\n",
    " '2016-09-08': 3.65,\n",
    " '2016-09-09': 3.6,\n",
    " '2016-09-10': 3.4,\n",
    " '2016-09-11': 3.49,\n",
    " '2016-09-12': 3.42,\n",
    " '2016-09-13': 3.6,\n",
    " '2016-09-14': 3.78,\n",
    " '2016-09-15': 3.98,\n",
    " '2016-09-16': 3.71,\n",
    " '2016-09-17': 3.3,\n",
    " '2016-09-18': 2.99,\n",
    " '2016-09-19': 3.3,\n",
    " '2016-09-20': 3.12,\n",
    " '2016-09-21': 3.5,\n",
    " '2016-09-22': 3.75,\n",
    " '2016-09-23': 3.88,\n",
    " '2016-09-24': 3.4,\n",
    " '2016-09-25': 3.22,\n",
    " '2016-09-26': 3.2,\n",
    " '2016-09-27': 3.2,\n",
    " '2016-09-28': 3.4,\n",
    " '2016-09-29': 3.95,\n",
    " '2016-09-30': 3.7,\n",
    " '2016-10-01': 3.13}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "932dc46c-3e0a-4acc-9958-5327ca2c5326",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vol = pd.DataFrame(d_shr.items(), columns=['t', 'volshare'])\n",
    "df_vol['t'] = pd.to_datetime(df_vol['t'])\n",
    "df_vol.to_stata(\"./Output/volshare_daily.dta\", write_index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "326eca18-550f-4059-b7d5-fed881fe9ebb",
   "metadata": {},
   "source": [
    "# monthly volume"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "410c7766-e7af-4595-ba47-ae6fca3117bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vol_state = pd.read_excel(\"./Input/australian_petroleum_statistics_-_issue_262_may_2018.xls\", \n",
    "                             sheet_name=\"Table 3B\", header=[4,5,6], index_col=0)\n",
    "# # df_vol_state.rename(columns={'Unnamed: 0_level_0':'date'}, inplace=True)\n",
    "df_vol_state = df_vol_state.loc[df_vol_state.index.notnull(), 'VIC (ML)']\n",
    "df_vol_state = df_vol_state['Automotive Gasoline']\n",
    "df_vol_state.rename(columns={'Regular\\n(<95 RON)\\n':'volstate'}, inplace=True)\n",
    "df_vol_state = df_vol_state.loc[df_vol_state.index[7:], 'volstate'].reset_index()\n",
    "df_vol_state.rename(columns={'index':'month'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77a2093a-24f0-4169-b731-0076ef17a79d",
   "metadata": {},
   "source": [
    "## share of total milage driven in Melbourne"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6c86c66-5d2c-4d61-adaa-afba96fbce96",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tz2tz = pd.read_csv(\"./Output/tz2tz_out.csv\")\n",
    "df_tz2tz.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc42ed17-6635-4649-b610-6d69060014fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_od = pd.read_csv(\"./Output/od_long_vic.csv\")\n",
    "df_od.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "459edfc1-1227-422b-be8d-955376c0e5f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"./Output/tzn2coor_mel.txt\", \"r\") as f:\n",
    "    data=f.read()\n",
    "d_tzn2coor={float(k):v for k, v in json.loads(data).items()} "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc1de5b7-def9-4481-9122-d64ee55a1fc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_od_n = df_od.merge(df_tz2tz, on=['tzn0', 'tzn1'], how='left').copy()\n",
    "df_od_n['mel0'] = (df_od_n['tzn0'].isin(d_tzn2coor.keys())).astype(int)\n",
    "df_od_n['mel1'] = (df_od_n['tzn1'].isin(d_tzn2coor.keys())).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf2eff6e-6c4c-44ca-a2d4-cc1a689e754a",
   "metadata": {},
   "outputs": [],
   "source": [
    "dist_vic = (df_od_n['n'] * df_od_n['distance']).sum()\n",
    "print(f\"total mileage driven in vic is {dist_vic/1000} km\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "953cd181-8181-4ff1-9dfc-acae4655a3a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_od_mel = df_od_n[(df_od_n['mel0']+df_od_n['mel1'])==2]\n",
    "\n",
    "dist_mel = (df_od_mel['n'] * df_od_mel['distance']).sum()\n",
    "print(f\"total mileage driven in mel is {dist_mel/1000} km\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b66e0d1a-fcdf-4a34-bd8a-b83501fa5e2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "shr = dist_mel / dist_vic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75d61b9e-9f7a-4787-80e5-51d12a84f0d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vol_state['volmel'] = df_vol_state['volstate'] * 0.513"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04c494b3-9ae5-4572-bdcc-7c313ca4dfc3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_vol_state.to_stata(\"./Output/voltot_monthly.dta\", write_index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90b2eec4-aa4d-4115-99cc-fe47b39f4c69",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
